package org.wangtian.youxueban.excel.children;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.wangtian.youxueban.excel.work.WeekDataBo;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ChildrenExcel {
    public static void main(String[] args) {
        List<String> list = getFileList("C:\\儿童统计\\xls");
        ChildrenExcel.read(list);
    }

    public static List<String> getFileList(String path) {
        List<String> list = new ArrayList();
        try {
            File file = new File(path);
            String[] filelist = file.list();
            for (int i = 0; i < filelist.length; i++) {
                list.add(path + "\\" + filelist[i]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void read(List<String> fileFullNames) {
        int x = 0;
        for (String file : fileFullNames) {
            if (x == 1) {
//                break;
            }
            System.out.println("############begin#################");
            System.out.println(file);
            System.out.println("############begin#################");
            File f = new File(file);    //工作薄
            Workbook workbook = null;
            try {
                workbook = Workbook.getWorkbook(f);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (BiffException e) {
                e.printStackTrace();
            }
            //2:获取第一个工作表sheet
            Sheet[] sheets = workbook.getSheets();
            for (Sheet sheet : sheets) {

                //3:获取数据
                System.out.println("行：" + sheet.getRows());
                System.out.println("列：" + sheet.getColumns());
                List<WeekDataBo> weekDataBos = new ArrayList<>();
//            Double[][] weekDataBoArrays = new Double[12][maxWeek];
                for (int i = 3; i < 7; i++) {
                    for (int j = 3; j < 4; j++) {
                        Cell cell = sheet.getCell(j, i); //j第几列，i第几行
//                System.out.print(cell.getContents() + " ");
                        double value = 0;
                        if (cell.getContents() != null && !"".equals(cell.getContents().trim())) {
                            value = Double.parseDouble(cell.getContents());
                        }
                        WeekDataBo weekDataBo = new WeekDataBo(j, i, value);
                        weekDataBos.add(weekDataBo);
                    }
                }

                String sheetName = sheet.getName();
                ChildrenExcel.changeValues(sheetName, weekDataBos);
                x++;
            }
            workbook.close();
        }

    }


    public static void changeValues(String sheetName, List<WeekDataBo> weekDataBos) {
        File f = new File("C:\\儿童统计\\儿童汇总.xls");    //工作薄
        int x = 0, y = 0;
        try {
            Workbook wb = null;        //工作薄
            WritableWorkbook wwb = null;    //可写工作薄
            Sheet s = null;                    //工作表
            WritableSheet ws = null;        //可写工作表
            //构造Workbook（工作薄）对象
            wb = Workbook.getWorkbook(f);            //
            wwb = Workbook.createWorkbook(f, wb);    //打开一个副本，并把制定数据写回到原表
            for (WeekDataBo weekDataBo : weekDataBos) {
                Double value = weekDataBo.getValue();
                ChildrenExcel.getNewPosition(weekDataBo,sheetName);
                int column = weekDataBo.getColumn();
                int row = weekDataBo.getRow();

                //获取一个工作表
                ws = wwb.getSheet("儿童汇总");
                WritableCell cell = ws.getWritableCell(column, row);//获取第一个单元格
                CellFormat cf = cell.getCellFormat();//获取第一个单元格的格式
                jxl.write.Number lbl = new jxl.write.Number(column, row,
                        value, cf);
                lbl.setCellFormat(cf);//将修改后的单元格的格式设定成跟原来一样
                ws.addCell(lbl);//将改过的单元格保存到sheet
            }

            wwb.write();
            wwb.close();
            System.out.println("##########" + sheetName + "执行成功#################");
        } catch (Exception e) {
//            System.out.println("x=" + x + ",y=" + y);
            System.out.println(sheetName);
            e.printStackTrace();
            System.exit(0);
        }
    }

    private  static void getNewPosition(WeekDataBo weekDataBo, String sheetName) {
//        String[][] dadas = new String[][]{{"一大区", "3"}, {"", ""}};
        weekDataBo.setColumn(weekDataBo.getRow()-1);
        if (sheetName.equals("一大区")) {
            weekDataBo.setRow(2);
        }else if(sheetName.equals("二大区")){
            weekDataBo.setRow(6);
        }else if(sheetName.equals("三大区")){
            weekDataBo.setRow(3);
        }else if(sheetName.equals("四大区")){
            weekDataBo.setRow(4);
        }else if(sheetName.equals("五大区")){
            weekDataBo.setRow(9);
        }else if(sheetName.equals("六大区")){
            weekDataBo.setRow(12);
        }else if(sheetName.equals("七大区")){
            weekDataBo.setRow(13);
        }else if(sheetName.equals("八大区")){
            weekDataBo.setRow(7);
        }else if(sheetName.equals("九大区")){
            weekDataBo.setRow(8);
        }else if(sheetName.equals("十大区")){
            weekDataBo.setRow(15);
        }else if(sheetName.equals("十一大区")){
            weekDataBo.setRow(16);
        }else if(sheetName.equals("十二大区")){
            weekDataBo.setRow(10);
        }else if(sheetName.equals("十三大区")){
            weekDataBo.setRow(17);
        }else if(sheetName.equals("十四大区")){
            weekDataBo.setRow(18);
        }else if(sheetName.equals("十五大区")){
            weekDataBo.setRow(5);
        }else if(sheetName.equals("十六大区")){
            weekDataBo.setRow(11);
        }else if(sheetName.equals("十七大区")){
            weekDataBo.setRow(14);
        }
    }
}
